﻿using System;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using ApexSql.Code.SqlServer;

namespace cgu
{
	public class CommonUtil
	{
		private const string EXCLUDE_COLUMN_ENDING_WITH = "_temp";
		private const bool CHANGE_UNDERSCORE_TO_PASCAL_CASE = true;
		private static readonly string[] arReadOnlyFieldNames = new[] { "ModifiedDate", "InsertDate", "ModifiedUser", "InsertUser"};

		public enum enmAdoParamValueSource
		{
			enmScalarType,
			enmEntityType,
			enmCurrentUser,
			enmOutputParam,
			enmTypeNone
		}


		// ************************************************************************************
		#region Functions returning a boolean result
		// ************************************************************************************

		/// <summary>
		/// primarily used to indicate if the C# code can set a
		/// objColumn of this data type to 'null' (a reference type)
		/// </summary>
		/// <param name="objColumn">ApexSql Code Column object</param>
		public static bool IsColumnReferenceDataType(Column objColumn)
		{
			switch (objColumn.AdoNetMapping.CLRType)
			{
				case "String":
				case "Byte[]":
					return true;

				default:
					return false;
			}
		}

		/// <summary>
		/// Determines if the column is a 'numeric' data type.
		/// </summary>
		/// <param name="objColumn">ApexSql Code Column object</param>
		public static bool IsColumnNumericType(Column objColumn)
		{
			switch (objColumn.SQLDatatype)
			{
				case "int":
				case "money":
				case "numeric":
				case "bigint":
				case "bit":
				case "decimal":
				case "float":
				case "real":
				case "smallint":
				case "smallmoney":
				case "tinyint": return true;
				default: return false;
			}
		}

		/// <summary>
		/// Determines if column is of a string type whos length can be checked
		/// </summary>
		/// <param name="objColumn">ApexSql Code Column object</param>
		public static bool IsColumnStringType(Column objColumn)
		{
			string sSqlDataType = objColumn.SQLDatatype;

			switch (sSqlDataType)
			{
				case "char":
				case "varchar":
				case "nchar":
				case "nvarchar":
					return true;

				case "byte":
				case "binary":
				case "varbinary":
				case "smallint":
				case "int":
				case "bigint":
				case "money":
				case "smallmoney":
				case "datetime":
				case "smalldatetime":
				case "decimal":
				case "numeric":
				case "float":
				case "real":
				case "tinyint":
				case "bit":
				case "cursor":
				case "ntext":
				case "image":
				case "sql_variant":
				case "table":
				case "timestamp":
				case "uniqueidentifier":
				case "text":
				case "xml":

					return false;

				default:
					throw new Exception(sSqlDataType + " is not a recognized datatype.");
			}
		}

		/// <summary>
		/// Determines if the given column name should be included in various lists.
		/// </summary>
		/// <param name="sColumnName"></param>
		/// <param name="bExcludeTempFields"></param>
		/// <returns></returns>
		public static bool ColumnExcludeCheck(string sColumnName, bool bExcludeTempFields)
		{
			return (bExcludeTempFields && sColumnName.EndsWith(EXCLUDE_COLUMN_ENDING_WITH));
		}


		/// <summary>
		/// Determines if, based on a set of rules, column is automatically filled (and updates should be restricted).
		/// </summary>
		/// <param name="objColumn"></param>
		/// <param name="bDateStampFieldsAreReadOnly"></param>
		/// <returns></returns>
		public static bool ColumnIsAutoFilled(Column objColumn, bool bDateStampFieldsAreReadOnly)
		{
			return (objColumn.IsIdentity || objColumn.IsTimeStamp || 
									(bDateStampFieldsAreReadOnly && (Array.IndexOf(arReadOnlyFieldNames, objColumn.Name) != -1))
						);
		}

		/// <summary>
		/// Compares 2 ColumnCollections and returns true if column names match
		/// </summary>
		/// <param name="Key1Cols"></param>
		/// <param name="Key2Cols"></param>
		/// <returns></returns>
		public static bool CompareKeyColumnNames(ColumnCollection Key1Cols, ColumnCollection Key2Cols)
		{
			//see if column counts match
			if (Key1Cols.Count != Key2Cols.Count)
			{
				return false;
			}

			//see if column names match
			for (int j = 0; j < Key1Cols.Count; j++)
			{
				if (Key1Cols[j].Name != Key2Cols[j].Name)
				{
					return false;
				}
			}

			//then we must have a match
			return true;
		}

		/// <summary>
		/// Determines if the given Index Key is the Primary Key 
		/// </summary>
		/// <param name="idxKey"></param>
		/// <param name="tbl"></param>
		/// <returns></returns>
		public static bool IndexSameAsPrimaryKey(Index idxKey, Table tbl)
		{	
			//skip this index if it is the primary key
			if (CompareKeyColumnNames(idxKey.IndexedColumns, tbl.PrimaryKeyColumns))
			{
				return true;
			}

			//no match
			return false;
		}


		/// <summary>
		/// Determines if the given Index Key is the same as a Foreign Key
		/// </summary>
		/// <param name="idxKey"></param>
		/// <param name="tbl"></param>
		/// <returns></returns>
		public static bool IndexSameAsForeignKey(Index idxKey, Table tbl)
		{
			//see if in foreign key
			foreach (Key fKey in tbl.ForeignKeys)
			{
				//see if the index keys and current foreign key match
				if (cgu.CommonUtil.CompareKeyColumnNames(fKey.KeyColumns, idxKey.IndexedColumns))
				{
					return true;
				}
			}

			//no match
			return false;
		}	

		// ************************************************************************************
		#endregion Functions returning a boolean result
		// ************************************************************************************

		// ************************************************************************************
		#region String formatting functions
		// ************************************************************************************

		/// <summary>
		/// Returns entity class name for the given table name
		/// </summary>
		/// <param name="objTable"></param>
		/// <returns></returns>
		public static string GetTableClassName(Table objTable)
		{
			//uppercase the first character
			return objTable.Name.Substring(0, 1).ToUpper() + objTable.Name.Substring(1);
		}

		/// <summary>
		/// Get the camel cased version of a name.  
		/// </summary>
		/// <param name="sName">Name to be changed</param>
		/// <returns>CamelCased version of the name</returns>
		public static string GetCamelCaseName(string sName)
		{
			if (sName.IndexOf("_") == -1 && sName.Equals(sName.ToUpper()))
			{
				sName = sName.ToLower().Replace(" ", "");
			}

			// first get the PascalCase version of the name
			string pascalName = GetPascalCaseName(sName);
			// now lowercase the first character to transform it to camelCase
			return pascalName.Substring(0, 1).ToLower() + pascalName.Substring(1);
		}

		/// <summary>
		/// Get the Pascal cased version of a name.  
		/// </summary>
		/// <param name="sName">Name to be changed</param>
		/// <returns>PascalCased version of the name</returns>
		public static string GetPascalCaseName(string sName)
		{
			//return empty string
			if (sName.Length == 0) return sName;

			//split string into words
			string[] Words;
			if (!CHANGE_UNDERSCORE_TO_PASCAL_CASE)
			{
				char[] splitter = { ' ' };
				Words = sName.Split(splitter);
			}
			else
			{
				char[] splitter = { '_', ' ' };
				Words = sName.Split(splitter);
			}
	    
			//loop through each word in the array
			for (int i = 0; i < Words.Length; i++)
			{
				//if the current word is greater than 1 character long
				if (Words[i].Length > 0)
				{
						//grab the current word
						string word = Words[i];

						//convert the first letter in the word to uppercase
						char firstLetter = char.ToUpper(word[0]);

						//concantenate the uppercase letter to the rest of the word
						Words[i] = firstLetter + word.Substring(1);
				}
			}

			//return the converted text
			return string.Join(string.Empty, Words);
		}

		/// <summary>
		/// Applies "?" shorthand to identify type as nullable
		/// </summary>
		/// <param name="objColumn"></param>
		/// <returns></returns>
		public static string GetNullableColumnTypeDef(Column objColumn)
		{
			string sClrType = objColumn.AdoNetMapping.CLRType;

			if (objColumn.IsNullable && !cgu.CommonUtil.IsColumnReferenceDataType(objColumn))
			{
				if (sClrType == "Byte[]")
				{
					sClrType = "Byte?[]";
				}
				else
				{
					sClrType += "?";
				}
			}

			return sClrType;
		}

		/// <summary>
		/// Get's the default value of a column based on column data type
		/// </summary>
		/// <param name="objColumn">DB table column to be checked</param>
		/// <returns>string representation of the default value</returns>
		public static string GetColumnDefaultValue(Column objColumn)
		{
			// for sql server
			if ((objColumn.SQLDefault != null) && (objColumn.SQLDefault.TextBody != ""))
			{
				string sDefault = objColumn.SQLDefault.TextBody;
				sDefault = sDefault.Replace("getdate()", "DateTime.Now");
				sDefault = sDefault.Replace("newid()", "Guid.NewGuid()");

				while (sDefault.StartsWith("(") && sDefault.EndsWith(")"))
					sDefault = sDefault.TrimStart('(').TrimEnd(')');

				if (objColumn.SQLDatatype == "boolean")
					sDefault = sDefault.Contains("1") ? "true" : "false";
				else if (!cgu.CommonUtil.IsColumnNumericType(objColumn) || sDefault.IndexOf("DateTime.Now") > -1 || sDefault.IndexOf("Guid.NewGuid()") > -1)
					sDefault = string.Format("\"{0}\"", sDefault);

				return sDefault;
			}

			return "";
		}

		/// <summary>
		/// Transform the name of a column into a public class property name.
		/// </summary>
		public static string GetColumnPropertyName(Column column)
		{
			//call the string-based version
			return GetColumnPropertyName(column.Name);
		}

		/// <summary>
		/// Transform a name into a public class property name.
		/// </summary>
		public static string GetColumnPropertyName(string sName)
		{
			sName = Regex.Replace(sName, @"[\W]", "");
			sName = sName.TrimStart(new[] { '_', '-', '+', '=', '*' });
			sName = cgu.CommonUtil.GetPascalCaseName(sName);

			return sName;
		}

		/// <summary>
		/// Fetch standard prefix for type
		/// </summary>
		public static string GetColumnPrefixedPropertyName(Column objColumn)
		{
			string sPrefix;

			switch (objColumn.AdoNetMapping.CLRType)
			{
				case "Byte[]": sPrefix = "byte"; break;
				case "Boolean": sPrefix = "bool"; break;
				case "String": sPrefix = "s"; break;
				case "Byte": sPrefix = "byte"; break;
				case "Double": sPrefix = "dbl"; break;
				case "Int64": sPrefix = "l"; break;
				case "Guid": sPrefix = "guid"; break;
				case "Decimal": sPrefix = "dec"; break;
				case "Object": sPrefix = "obj"; break;
				case "DateTime": sPrefix = "date"; break;
				case "Single": sPrefix = "sgl"; break;
				case "Int32": sPrefix = "i"; break;
				case "Int16": sPrefix = "i"; break;
				default:
					throw new Exception(objColumn.AdoNetMapping.CLRType + " is not a recognized datatype.");
			}

			return sPrefix + GetColumnPropertyName(objColumn);
		}

		/// <summary>
		/// Get the safe name for a data object by determining if it contains spaces or other illegal
		/// characters - if so wrap with []
		/// </summary>
		/// <param name="objectName">The name of the database schema object</param>
		/// <returns>The safe name of the object</returns>
		public static string GetSqlSafeName(string objectName)
		{
			return objectName.IndexOfAny(new[] { ' ', '@', '-', ',', '!' }) > -1 ? "[" + objectName + "]" : objectName;
		}

		/// <summary>
		/// Convert database types to C# types, without nullable support.
		/// </summary>
		/// <param name="objColumn">Column or parameter</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public static string GetCSharpTypeWithoutNullable(Column objColumn)
		{
			string sSqlDataType = objColumn.SQLDatatype;

			if (objColumn.SQLDatatype.ToLower() == "real")
				return "System.Single";

			if (objColumn.SQLDatatype.ToLower() == "xml")
				return "string";

			return sSqlDataType;
		}

		// ************************************************************************************
		#endregion String formatting functions
		// ************************************************************************************

		// ************************************************************************************
		#region non-List-oriented parameter functions
		// ************************************************************************************

		/// <summary>
		///  Get System.ComponentModel.DataObjectFieldAttribute based on the schema information on a column.
		/// </summary>
		/// <param name="objColumn"></param>
		/// <returns></returns>
		public static string GetDataObjectFieldAttributes(Column objColumn)
		{
			///	1. indicates whether the field is the primary key 
			/// 2. whether the field is a database identity field
			/// 3. whether the field can be null
			/// 4. sets the length of the field
			return string.Format("{0}, {1}, {2}{3}",
				/*0*/ objColumn.IsInPrimaryKey.ToString().ToLower(),
				/*1*/ objColumn.IsIdentity.ToString().ToLower(),
				/*2*/ objColumn.IsNullable.ToString().ToLower(),
				/*3*/ (cgu.CommonUtil.IsColumnStringType(objColumn) ? ", " + objColumn.Length.ToString() : ""));
		}

		/// <summary>
		/// Gets default database value by column data type
		/// </summary>
		/// <param name="objColumn"></param>
		/// <returns></returns>
		internal static string GetDefaultByDatatype(Column objColumn)
		{
			switch (objColumn.AdoNetMapping.CLRType)
			{
				case "Byte[]": return "new byte[] { }";
				case "Boolean": return "false";
				case "String": return "string.Empty";
				case "Byte": return "(byte)0";
				case "Double": return "0f";
				case "Int64": return "(long)0";
				case "Guid": return "Guid.Empty";
				case "Decimal": return "0m";
				case "Object": return "null";
				case "DateTime": return "DateTime.MinValue";
				case "Single": return "0F";
				case "Int32": return "0";
				case "Int16": return "(short)0";
				default:
					throw new Exception(objColumn.AdoNetMapping.CLRType + " is not a recognized datatype.");
			}
		}

		/// <summary>
		/// Returns the appropriate 'null' value representation, most importantly for columns the are not nullable
		/// </summary>
		/// <param name="objColumn"></param>
		/// <returns></returns>
		internal static string GetColumnIsDBNullValue(Column objColumn)
		{
			if (objColumn.IsNullable)
				return "null";

			return GetDefaultByDatatype(objColumn);
		}

		/// <summary>
		/// Builds the library function call to add the appropriate parameter call to the list.
		/// </summary>
		internal static string GetAdoParameterFunctionCallForColumn(Column objColumn, enmAdoParamValueSource enmValueType, int iIndentLevel, string sLibraryNameSpace, ParameterDirection enmDirection)
		{
			StringBuilder sBuilder = new StringBuilder();
			sBuilder.Append("\r\n");
			sBuilder.Append('\t', iIndentLevel);

			//allow the ado parameter value to come from a scalar parameter, an entity parameter, or lookup value
			string sParamValueAndLength;
			string sParamNamePrefix = "";
			switch (enmValueType)
			{
				case enmAdoParamValueSource.enmOutputParam: sParamValueAndLength = ""; sParamNamePrefix = "out_"; break;
				case enmAdoParamValueSource.enmCurrentUser: sParamValueAndLength = ", " + sLibraryNameSpace + ".FormUtil.GetUserName()"; break;
				case enmAdoParamValueSource.enmScalarType: sParamValueAndLength = ", " + GetColumnPropertyName(objColumn); break;
				case enmAdoParamValueSource.enmEntityType: sParamValueAndLength = ", entity." + GetColumnPropertyName(objColumn); break;
				default:
					throw new Exception("Unrecognized param value source: " + enmValueType.ToString());
			}

			//if a string type, add the length
			if (objColumn.AdoNetMapping.CLRType == "String")
			{
				sParamValueAndLength += ", " + objColumn.Length.ToString();
			}

			//call appropriate function for the direction and type
			string sDirection;
			string sType = "";
			switch (enmDirection)
			{
				case ParameterDirection.Input: 
							sDirection = "Input";
							sType = "<" + objColumn.AdoNetMapping.CLRType + ((objColumn.IsNullable && !cgu.CommonUtil.IsColumnReferenceDataType(objColumn))? "?" : "") + ">"; 
							break;
				case ParameterDirection.Output: sDirection = "Output"; break;
				case ParameterDirection.ReturnValue: sDirection = "ReturnValue"; break;
				default:
					throw new Exception("Param direction is not supported: " + enmDirection.ToString());
			}

			//build input def
			//sdf.Code.DbUtil.AddParamInput<Int32>(cmd, SqlDbType.Int, "@AddressElectronicId", entity.AddressElectronicId);
			sBuilder.AppendFormat(
															"{0}.DbUtil.AddParam{1}{2}(cmd, {3}, \"@{4}{5}\" {6});",
															sLibraryNameSpace,
															sDirection,
															sType,
															objColumn.AdoNetMapping.SqlDbType,
															sParamNamePrefix,
															cgu.CommonUtil.GetColumnPropertyName(objColumn.Name), 
															sParamValueAndLength);

			//return the line
			return sBuilder.ToString();
		}

		/// <summary>
		/// Returns mapped chsharp type for column
		/// </summary>
		/// <returns></returns>
		internal static string GetCSharpType(Column objColumn)
		{
			return objColumn.GetMappedType("C#");
		}
		internal static string GetCSharpType(Parameter objParam)
		{
			return objParam.GetMappedType("C#");
		}

		/// Returns mapped chsharp type for column
		internal static string GetClrType(Column objColumn)
		{
			return objColumn.AdoNetMapping.CLRType;
		}

		// ************************************************************************************
		#endregion non-List-oriented parameter functions
		// ************************************************************************************

	}
}
